package com.upc.cajabancos.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import oracle.jdbc.driver.OracleResultSet;
import oracle.jdbc.driver.OracleTypes;

import com.upc.cajabancos.model.Cliente;
import com.upc.cajabancos.model.DocumentoPago;
import com.upc.cajabancos.model.TipoDocumentoPago;
import com.upc.cajabancos.util.DataAccessObjetc;

public class DocumentoPagoDAO {
	
	private Connection connection = null;
	
	public DocumentoPagoDAO(Connection connection){
		this.connection = connection;
	}
	
	/**
	 * Registra documento de pago de una orden de pago
	 * @author Cesar Jesus
	 * @since 30/01/2011
	 * @param documentoPago
	 */	
	public String registrar(DocumentoPago documentoPago) throws Exception{
		
		String numero = null;
		CallableStatement cst= null;
		
		try{
			
			cst= connection.prepareCall(DataAccessObjetc.buildProcedureCall("REGISTRAR_DOCUMENTOPAGO",6));
			cst.registerOutParameter(1, Types.VARCHAR);
			cst.setString(2, documentoPago.getTipo().getCodigo());
			cst.setString(3, documentoPago.getEstado());
			cst.setString(4, documentoPago.getCliente().getCodigo());
			cst.setDate(5, new java.sql.Date(documentoPago.getFechaEmision().getTime()));
			cst.setDouble(6, documentoPago.getMontoPagar());
			//cst.setDouble(7, documentoPago.getMontoIGV());
			
			cst.execute();
			
			numero = cst.getString(1);
			
		}
		catch(Exception e){
			e.printStackTrace();
			throw e;
		}
		finally{
			
			DataAccessObjetc.close(null, cst, null);
		}
		
		return numero;
	}
	
	/**
	 * Busca documentos de pago
	 * @author Cesar Jesus
	 * @since 30/01/2011
	 * @param tipo
	 * @param estado
	 * @param idCliente
	 * @param desde
	 * @param hasta
	 * @return List<DocumentoPago>
	 */	
	public List<DocumentoPago> buscar(String tipo, String estado, String idCliente, Date desde, Date hasta) throws Exception{
		CallableStatement cstmt = null;	
		ResultSet rs = null;
		DocumentoPago documentoPago = null;
		List<DocumentoPago> listaDocumentoPagos = new ArrayList<DocumentoPago>();
    	
    	try{
			
			cstmt = connection.prepareCall(DataAccessObjetc.buildProcedureCall("BUSCAR_DOCUMENTOSPAGO",6));
			
			if("".equals(tipo)){
				cstmt.setNull(1, OracleTypes.NULL);
			}
			else{
				cstmt.setString(1, tipo);
			}
			if("".equals(estado)){
				cstmt.setNull(2, OracleTypes.NULL);
			}
			else{
				cstmt.setString(2, estado);
			}
			if("".equals(idCliente)){
				cstmt.setNull(3, OracleTypes.NULL);
			}
			else{
				cstmt.setString(3, idCliente);
			}
			if(desde == null){
				cstmt.setNull(4, OracleTypes.NULL);
			}
			else{
				cstmt.setTimestamp(4,new Timestamp(desde.getTime()));
			}
			if(hasta == null){
				cstmt.setNull(5, OracleTypes.NULL);
			}
			else{
				cstmt.setTimestamp(5,new Timestamp(hasta.getTime()));
			}
			//cstmt.setTimestamp(++i,new Timestamp(Helper.getComienzoDia(report.getDateini()).getTime()));
			//cstmt.setTimestamp(++i,new Timestamp(Helper.getFinDia(report.getDateend()).getTime()));
			cstmt.registerOutParameter(6, OracleTypes.CURSOR);
			cstmt.execute();
			rs = (OracleResultSet)cstmt.getObject(6);
			
			while (rs.next()){
				documentoPago = new DocumentoPago();
				documentoPago.setNumero(rs.getString("Co_NroDocumPago"));
				documentoPago.setTipo(new TipoDocumentoPago());
				documentoPago.getTipo().setDescripcion(rs.getString("NO_DescTipoDoc"));
				documentoPago.setCliente(new Cliente());
				documentoPago.getCliente().setNombre(rs.getString("No_Cliente"));
				documentoPago.setFechaEmision(rs.getDate("Fe_Fecha"));
				documentoPago.setMontoPagar(rs.getDouble("ss_montototal"));

				listaDocumentoPagos.add(documentoPago);
			}
			
    	}
    	catch(Exception e){
    		throw e;
    	}
    	finally {
    		DataAccessObjetc.close(null,cstmt,rs);
		}
    	
    	return listaDocumentoPagos;
    }

}
